--
-- PostgreSQL database dump
--

-- Dumped from database version 8.4.20
-- Dumped by pg_dump version 9.1.14
-- Started on 2014-11-14 13:00:30 ART

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 471 (class 2612 OID 61453)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: -
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


SET search_path = public, pg_catalog;

--
-- TOC entry 162 (class 1255 OID 61457)
-- Dependencies: 471 3
-- Name: desactivar_alumnos(timestamp without time zone); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION desactivar_alumnos(fecha timestamp without time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $$declare
  i integer;
  c integer;
  ultima_asistencia timestamp without time zone;

begin
  c := 0;
  for i in select alm_dni from alumno
  loop
    ultima_asistencia := (select max(ast_fechahora) from asistencia where ast_alumno = i);

    if ultima_asistencia < fecha then
      update alumno set alm_activo = false where alm_dni = i;
      c := c + 1;
    end if;   
  end loop;
  return c;
end;$$;


SET default_with_oids = false;

--
-- TOC entry 144 (class 1259 OID 28730)
-- Dependencies: 1718 1719 1720 1721 1722 3
-- Name: alumno; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE alumno (
    alm_dni integer NOT NULL,
    alm_apellido character varying(100) NOT NULL,
    alm_nombre character varying(100) NOT NULL,
    alm_fecha_nac date NOT NULL,
    alm_direccion character varying(200) NOT NULL,
    alm_barrio character varying(200) NOT NULL,
    alm_telefono character varying(100),
    alm_celular character varying(50),
    alm_presento_certificado boolean DEFAULT false NOT NULL,
    alm_presento_fotoc_dni boolean DEFAULT false NOT NULL,
    alm_presento_planilla boolean DEFAULT false NOT NULL,
    alm_fecha_alta date DEFAULT now() NOT NULL,
    alm_activo boolean DEFAULT true NOT NULL
);


--
-- TOC entry 146 (class 1259 OID 28743)
-- Dependencies: 1724 1725 3
-- Name: asistencia; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE asistencia (
    ast_cdg integer NOT NULL,
    ast_alumno integer NOT NULL,
    ast_deporte integer NOT NULL,
    ast_fechahora timestamp without time zone DEFAULT now() NOT NULL,
    ast_tipo character varying(20) NOT NULL,
    CONSTRAINT tipo_check CHECK ((((ast_tipo)::text = 'ENTRADA'::text) OR ((ast_tipo)::text = 'SALIDA'::text)))
);


--
-- TOC entry 145 (class 1259 OID 28741)
-- Dependencies: 146 3
-- Name: asistencia_ast_cdg_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE asistencia_ast_cdg_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 1837 (class 0 OID 0)
-- Dependencies: 145
-- Name: asistencia_ast_cdg_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE asistencia_ast_cdg_seq OWNED BY asistencia.ast_cdg;


--
-- TOC entry 141 (class 1259 OID 28682)
-- Dependencies: 3
-- Name: deporte; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE deporte (
    dpt_cdg integer NOT NULL,
    dpt_nombre character varying(50) NOT NULL,
    dpt_descripcion character varying(100),
    dpt_horarios character varying(100),
    dpt_espacio character varying(100) NOT NULL
);


--
-- TOC entry 143 (class 1259 OID 28724)
-- Dependencies: 3
-- Name: deporte_alumno; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE deporte_alumno (
    almdpt_cdg integer NOT NULL,
    almdpt_alm integer NOT NULL,
    almdpt_dpt integer NOT NULL
);


--
-- TOC entry 142 (class 1259 OID 28722)
-- Dependencies: 143 3
-- Name: deporte_alumno_almdpt_cdg_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE deporte_alumno_almdpt_cdg_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 1838 (class 0 OID 0)
-- Dependencies: 142
-- Name: deporte_alumno_almdpt_cdg_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE deporte_alumno_almdpt_cdg_seq OWNED BY deporte_alumno.almdpt_cdg;


--
-- TOC entry 140 (class 1259 OID 28680)
-- Dependencies: 3 141
-- Name: deporte_dpt_cdg_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE deporte_dpt_cdg_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 1839 (class 0 OID 0)
-- Dependencies: 140
-- Name: deporte_dpt_cdg_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE deporte_dpt_cdg_seq OWNED BY deporte.dpt_cdg;


--
-- TOC entry 147 (class 1259 OID 36872)
-- Dependencies: 3
-- Name: espacio; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE espacio (
    esp_nombre character varying(100) NOT NULL,
    esp_descripcion character varying(200)
);


--
-- TOC entry 148 (class 1259 OID 53263)
-- Dependencies: 1829 3
-- Name: vw_almdpt; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_almdpt AS
    SELECT alumno.alm_dni, alumno.alm_apellido, alumno.alm_nombre, alumno.alm_fecha_nac, deporte.dpt_cdg, deporte.dpt_nombre, deporte.dpt_descripcion, deporte.dpt_horarios, deporte.dpt_espacio, deporte_alumno.almdpt_cdg, alumno.alm_activo, alumno.alm_direccion, alumno.alm_barrio, alumno.alm_telefono, alumno.alm_celular FROM alumno, deporte, deporte_alumno WHERE ((deporte_alumno.almdpt_alm = alumno.alm_dni) AND (deporte_alumno.almdpt_dpt = deporte.dpt_cdg));


--
-- TOC entry 149 (class 1259 OID 53271)
-- Dependencies: 1830 3
-- Name: vw_asistencia; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW vw_asistencia AS
    SELECT asistencia.ast_fechahora, asistencia.ast_tipo, alumno.alm_apellido, alumno.alm_nombre, alumno.alm_dni, deporte.dpt_cdg, deporte.dpt_nombre, deporte.dpt_descripcion, deporte.dpt_horarios, deporte.dpt_espacio FROM asistencia, alumno, deporte WHERE ((asistencia.ast_alumno = alumno.alm_dni) AND (asistencia.ast_deporte = deporte.dpt_cdg)) ORDER BY asistencia.ast_fechahora;


--
-- TOC entry 1723 (class 2604 OID 28746)
-- Dependencies: 146 145 146
-- Name: ast_cdg; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY asistencia ALTER COLUMN ast_cdg SET DEFAULT nextval('asistencia_ast_cdg_seq'::regclass);


--
-- TOC entry 1716 (class 2604 OID 28685)
-- Dependencies: 141 140 141
-- Name: dpt_cdg; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY deporte ALTER COLUMN dpt_cdg SET DEFAULT nextval('deporte_dpt_cdg_seq'::regclass);


--
-- TOC entry 1717 (class 2604 OID 28727)
-- Dependencies: 143 142 143
-- Name: almdpt_cdg; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY deporte_alumno ALTER COLUMN almdpt_cdg SET DEFAULT nextval('deporte_alumno_almdpt_cdg_seq'::regclass);


--
-- TOC entry 1735 (class 2606 OID 28740)
-- Dependencies: 144 144 1832
-- Name: alm_pk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY alumno
    ADD CONSTRAINT alm_pk PRIMARY KEY (alm_dni);


--
-- TOC entry 1731 (class 2606 OID 28729)
-- Dependencies: 143 143 1832
-- Name: almdpt_pk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY deporte_alumno
    ADD CONSTRAINT almdpt_pk PRIMARY KEY (almdpt_cdg);


--
-- TOC entry 1737 (class 2606 OID 28749)
-- Dependencies: 146 146 1832
-- Name: ast_pk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY asistencia
    ADD CONSTRAINT ast_pk PRIMARY KEY (ast_cdg);


--
-- TOC entry 1733 (class 2606 OID 45073)
-- Dependencies: 143 143 143 1832
-- Name: deporte_alumno_unique; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY deporte_alumno
    ADD CONSTRAINT deporte_alumno_unique UNIQUE (almdpt_alm, almdpt_dpt);


--
-- TOC entry 1727 (class 2606 OID 28689)
-- Dependencies: 141 141 1832
-- Name: dpt_nombre_unique; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY deporte
    ADD CONSTRAINT dpt_nombre_unique UNIQUE (dpt_nombre);


--
-- TOC entry 1729 (class 2606 OID 28687)
-- Dependencies: 141 141 1832
-- Name: dpt_pk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY deporte
    ADD CONSTRAINT dpt_pk PRIMARY KEY (dpt_cdg);


--
-- TOC entry 1739 (class 2606 OID 36876)
-- Dependencies: 147 147 1832
-- Name: esp_pk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY espacio
    ADD CONSTRAINT esp_pk PRIMARY KEY (esp_nombre);


--
-- TOC entry 1741 (class 2606 OID 45089)
-- Dependencies: 1734 146 144 1832
-- Name: ast_alumno; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY asistencia
    ADD CONSTRAINT ast_alumno FOREIGN KEY (ast_alumno) REFERENCES alumno(alm_dni);


--
-- TOC entry 1742 (class 2606 OID 45094)
-- Dependencies: 141 146 1728 1832
-- Name: ast_deporte; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY asistencia
    ADD CONSTRAINT ast_deporte FOREIGN KEY (ast_deporte) REFERENCES deporte(dpt_cdg);


--
-- TOC entry 1740 (class 2606 OID 36877)
-- Dependencies: 1738 147 141 1832
-- Name: deporte_espacio_fk; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY deporte
    ADD CONSTRAINT deporte_espacio_fk FOREIGN KEY (dpt_espacio) REFERENCES espacio(esp_nombre);


-- Completed on 2014-11-14 13:00:31 ART

--
-- PostgreSQL database dump complete
--

